package My

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"db2s/dbPar"
	"db2s/global"
	"db2s/go-log/log"
	"db2s/outPut"
	"db2s/parDef"
	"db2s/ref"
	"reflect"
	"strings"
	"sync/atomic"
)

func newTimeTypeToFunc(colMap, columnName, asColumn string) (r string) {
	switch colMap {
	case "year-month-day":
		r = fmt.Sprintf("date_format(%v,'%%Y-%%m-%%d') as `%v`", columnName, asColumn)
	case "hour:minute:second":
		r = fmt.Sprintf("date_format(%v,'%%H:%%i:%%s') as `%v`", columnName, asColumn)
	case "year-month-day hour:minute:second":
		r = fmt.Sprintf("date_format(%v,'%%Y-%%m-%%d %%H:%%i:%%s') as `%v`", columnName, asColumn)
	case "hour:minute:second.milli(1)", "hour:minute:second.milli(2)", "hour:minute:second.milli(3)", "hour:minute:second.micro(1)", "hour:minute:second.micro(2)", "hour:minute:second.micro(3)", "hour:minute:second.nano(1)", "hour:minute:second.nano(2)", "hour:minute:second.nano(3)":
		r = fmt.Sprintf("date_format(%v,'%%H:%%i:%%s.%%f') as `%v`", columnName, asColumn)
	case "year":
		r = fmt.Sprintf("date_format(%v,'%%Y') as `%v`", columnName, asColumn)
	case "year-month-day hour:minute:second.milli(1)", "year-month-day hour:minute:second.milli(2)", "year-month-day hour:minute:second.milli(3)", "year-month-day hour:minute:second.micro(1)", "year-month-day hour:minute:second.micro(2)", "year-month-day hour:minute:second.micro(3)", "year-month-day hour:minute:second.nano(1)", "year-month-day hour:minute:second.nano(2)", "year-month-day hour:minute:second.nano(3)":
		r = fmt.Sprintf("date_format(%v,'%%Y-%%m-%%d %%H:%%i:%%s.%%f') as `%v`", columnName, asColumn)
	default:
		r = fmt.Sprintf("date_format(%v,'%%Y-%%m-%%d %%H:%%i:%%s') as `%v`", columnName, asColumn)
	}
	return
}
func newFloatTypeToFunc(precision, scale int64, columnName, asColumn string) (r string) {
	var decimal []string
	if precision > 0 {
		decimal = append(decimal, fmt.Sprintf("%v", precision))
	}
	if scale > 0 {
		decimal = append(decimal, fmt.Sprintf("%v", scale))
	}
	if len(decimal) > 0 {
		r = fmt.Sprintf("CAST(%v AS DECIMAL(%v)) as `%v`", columnName, strings.Join(decimal, ","), asColumn)
	} else {
		r = fmt.Sprintf("%v as `%v`", columnName, asColumn)
	}
	return
}

func newReadGisTypeToFunc(columnName, asColumn string) (r string) {
	return fmt.Sprintf(" \n"+
		"CASE \n    "+
		"WHEN %v IS NOT NULL \n    "+
		"THEN \n"+
		"concat("+
		"	ST_AsText(%v), '@STYPE:', ST_GeometryType(%v), ',','SRID:', ST_SRID(%v) , ',','AXIS:OGC'"+
		") \n    "+
		"ELSE NULL \n  "+
		"END AS %v ",
		columnName, columnName, columnName, columnName, asColumn)
}

func newTimeValueToFunc(colMap, value string) (r string) {
	switch colMap {
	case "year-month-day":
		r = fmt.Sprintf("DATE_FORMAT(%s,'%%Y-%%m-%%d')", value)
	case "hour:minute:second":
		r = fmt.Sprintf("DATE_FORMAT(%s,'%%H:%%i:%%s')", value)
	case "year-month-day hour:minute:second":
		r = fmt.Sprintf("DATE_FORMAT(%s,'%%Y-%%m-%%d %%H:%%i:%%s')", value)
	default:
		r = fmt.Sprintf("DATE_FORMAT(%s,'%%Y-%%m-%%d %%H:%%i:%%s')", value)
	}
	return
}
func newDecimalTypeToFunc(columnName, asColumn string) (r string) {
	r = fmt.Sprintf("0+CAST(%v as char)  as `%v`", columnName, asColumn)
	return
}
func NewSpecialTypeToFunc(LockCol parDef.Encryption, colMap parDef.ColMetaMapS) (r string) {
	asColumn := fmt.Sprintf("C%v", colMap.ColumnSeq)
	var unlockFunc string
	for _, v := range LockCol.Column {
		if strings.EqualFold(v, colMap.ColumnName) && len(LockCol.UnlockFunc) > 0 {
			unlockFunc = fmt.Sprintf("%v.%v(`%v`)", LockCol.Schema, LockCol.UnlockFunc, colMap.ColumnName)
			break
		}
	}
	if len(unlockFunc) == 0 {
		unlockFunc = fmt.Sprintf("`%v`", colMap.ColumnName)
	}
	switch colMap.TypeBelong {
	case "time", "timestamp":
		return newTimeTypeToFunc(colMap.Type.ColTypeMap, unlockFunc, asColumn)
	case "float", "double":
		return newFloatTypeToFunc(colMap.Type.Precision, colMap.Type.Scale, unlockFunc, asColumn)
	case "decimal":
		return newDecimalTypeToFunc(unlockFunc, asColumn)
	case "gis":
		return newReadGisTypeToFunc(unlockFunc, asColumn)
	default:
		return fmt.Sprintf("%v as `%v`", unlockFunc, asColumn)
	}
}

// NewSpecialCharValueToFunc 根据列的数据类型，将字符串值继续函数转换
func NewSpecialCharValueToFunc(colMap parDef.ColMetaMapS, value string) (newValue string) {
	newValue = strings.ReplaceAll(value, "'", "\\'")
	switch colMap.TypeBelong {
	case "time", "timestamp":
		return newTimeValueToFunc(colMap.Type.ColTypeMap, newValue)
	default:
		switch {
		case strings.EqualFold(colMap.ColumnName, "rowId"):
			newValue = value
		case strings.EqualFold(newValue, "NULL"):
			newValue = value
		}
		return
	}
}
func newHitSpecify(indexName, partitionName string) (hitIndexName string) {
	//指定partition name会导致无法使用索引，进行全表扫描，且该sql为查询数据，已经通过索引列范围确定查询范围，所以不需要指定分区
	if len(indexName) > 0 && !strings.EqualFold(partitionName, "single") && len(partitionName) > 0 {
		hitIndexName = fmt.Sprintf(" /*+ index(T %v) */ ", indexName)
	}
	return
}
func newLeftStitching(s parDef.WhereGenerateInput) (l string) {
	var whereLeft []string
	for _, v := range func() []map[string]string {
		if len(s.NewLeftDirection) > 0 {
			return s.NewLeftDirection
		}
		return getAtomicValue(s.LeftDirection)
	}() {
		columnName, nLeftOk := v["columnName"]
		columnDataLeft, cLeftOk := v["columnDataLeft"]
		querySymbolLeft, qLeftOk := v["querySymbolLeft"]
		if cLeftOk && qLeftOk && nLeftOk {
			if strings.EqualFold(querySymbolLeft, "=") && strings.EqualFold(columnDataLeft, "NULL") { //处理等值null
				whereLeft = append(whereLeft, fmt.Sprintf("`%s` %s %s", columnName, " IS ", columnDataLeft))
			} else if strings.EqualFold(querySymbolLeft, ">") && strings.EqualFold(columnDataLeft, "NULL") {
				whereLeft = append(whereLeft, fmt.Sprintf("`%s` %s %s", columnName, " IS NOT ", columnDataLeft))
			} else {
				whereLeft = append(whereLeft, fmt.Sprintf("`%s` %s %s", columnName, querySymbolLeft, NewSpecialCharValueToFunc(getSpecialColNameMetaMap(columnName, s.ColMetaMap), columnDataLeft)))
			}
		}
	}
	return strings.Join(whereLeft, " and ")
}
func getSpecialColNameMetaMap(name string, colMeta []parDef.ColMetaMapS) (r parDef.ColMetaMapS) {
	for _, col := range colMeta {
		if strings.EqualFold(col.ColumnName, name) {
			return col
		}
	}
	return
}
func newRightStitching(s parDef.WhereGenerateInput) string {
	var whereRight []string
	for _, v := range func() []map[string]string {
		if len(s.NewRightDirection) > 0 {
			return s.NewRightDirection
		}
		return getAtomicValue(s.RightDirection)
	}() {
		columnName, nRightOk := v["columnName"]
		columnDataRight, cRightOk := v["columnDataRight"]
		querySymbolRight, qRightOk := v["querySymbolRight"]
		if cRightOk && qRightOk && nRightOk {
			if strings.EqualFold(querySymbolRight, "=") && strings.EqualFold(columnDataRight, "NULL") {
				whereRight = append(whereRight, fmt.Sprintf("`%s` %s %s", columnName, " IS ", columnDataRight))
			} else if strings.EqualFold(querySymbolRight, ">") && strings.EqualFold(columnDataRight, "NULL") {
				whereRight = append(whereRight, fmt.Sprintf("`%s` %s %s", columnName, " IS NOT ", columnDataRight))
			} else {
				whereRight = append(whereRight, fmt.Sprintf("`%s` %s %s", columnName, querySymbolRight, NewSpecialCharValueToFunc(getSpecialColNameMetaMap(columnName, s.ColMetaMap), columnDataRight)))
			}
		}
	}
	return strings.Join(whereRight, " and ")
}
func newWhereGenerate(whereSql string, s parDef.WhereGenerateInput) (ConditionSlice []string, err error) {
	if len(whereSql) > 0 {
		ConditionSlice = append(ConditionSlice, whereSql)
		return
	}
	if v := newLeftStitching(s); len(v) > 0 {
		ConditionSlice = append(ConditionSlice, v)
	}
	if v := newRightStitching(s); len(v) > 0 {
		ConditionSlice = append(ConditionSlice, v)
	}
	return
}
func newOrderBY(s parDef.WhereGenerateInput) (res string, err error) {
	var orderByColumn []string
	if strings.EqualFold(s.NullConstraint, "null") {
		return
	}
	var uniqOrderByColumn = make(map[string]int)
	for _, v := range s.IndexColumn {
		for _, v1 := range getAtomicValue(s.LeftDirection) {
			if v2, ok := v1["columnName"]; ok {
				if strings.EqualFold(v, v2) {
					if _, ok2 := uniqOrderByColumn[v]; !ok2 {
						uniqOrderByColumn[v]++
						orderByColumn = append(orderByColumn, v)
					}
				}
			}
		}
	}
	if len(orderByColumn) > 0 {
		return fmt.Sprintf("order by `%v`", strings.Join(orderByColumn, "`,`")), nil
	}
	return
}
func newResultCol(asSelectColumn []string, tableAS string) (r string) {
	var l []string
	for _, v := range asSelectColumn {
		l = append(l, fmt.Sprintf("%v.`%v` ", tableAS, v))
	}
	return strings.Join(l, ",")
}
func newSelectCol(selectC []string) (r string) {
	var selectIndexColumn []string
	for _, v := range selectC {
		selectIndexColumn = append(selectIndexColumn, fmt.Sprintf("\"%v\"", v))
	}
	return strings.Join(selectIndexColumn, ",")
}
func newOrderByNull(indexCol []string) (r string) {
	return fmt.Sprintf(" order by `%v`", strings.Join(indexCol, "`,`"))
}
func newWhereSplicing(whereCondition []string) (r string) {
	if len(whereCondition) > 0 {
		return fmt.Sprintf(" and %v", strings.Join(whereCondition, " and "))
	}
	return
}
func getIndexColMetaCharset(ctx context.Context) (collation string, compareVal []string) {
	switch meta := parDef.GetFuncParameterValue(ctx, parDef.GetMetaParameterValue()); meta.(type) {
	case map[string][]string:
		for col, val := range meta.(map[string][]string) {
			for _, subCol := range parDef.GetFuncParameterValue(ctx, parDef.GetTableIndexColumnMapParameterValue()).([]parDef.IdxSubColumnMe) {
				if strings.EqualFold(col, subCol.ColumnName) {
					collation = subCol.Collation
					compareVal = val
					break
				}
			}
		}
	}
	return
}
func getColCharacterVarchar(ctx context.Context) (newCompareVal []string) {
	var collation, compareVal = getIndexColMetaCharset(ctx)
	for _, v := range compareVal {
		var notNullVal = v
		if strings.EqualFold(v, "<null>") {
			notNullVal = "NULL"
		}
		newCompareVal = append(newCompareVal, fmt.Sprintf("'%v' COLLATE %v", notNullVal, collation))
	}
	return
}
func (rows RowsF) getInsertIntoColumnPrefix(ctx context.Context) (insertColumn [][]string, err error) {
	var result global.Return
	var event = "[getInsertIntoColumnPrefix]"
	if result, err = rows.InsertAllCol(ctx); err != nil {
		err = ref.ErrAddPrintf(event, err)
		return
	}
	switch result.Result.(type) {
	case [][]string:
		insertColumn = result.Result.([][]string)
	default:
		err = ref.ErrAddPrintf(event, errors.New(fmt.Sprintf("type not match. curry type is %v", reflect.TypeOf(result.Result))))
	}
	return
}
func (rows RowsF) Prefix(ctx context.Context) (result global.Return, err error) {
	var event = "[Prefix]"
	var tableName, ignoreStr string
	var insertColumn [][]string
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	return ctxDoneExecFunc(ctx, func() (global.Return, error) {
		//"IGNORE" //timestamp类型 Incorrect datetime value: '2024-09-26 11:27:28 +0800 08:00' for column
		if insertColumn, err = rows.getInsertIntoColumnPrefix(ctx); err != nil {
			err = ref.ErrAddPrintf(event, err)
			return result, err
		}
		var schema, table = parDef.GetExecFuncParameterValueBase(ctx)
		tableName = fmt.Sprintf(" `%v`.`%v` ", schema, table)
		result.Result = fmt.Sprintf("%s ", fmt.Sprintf("INSERT %v INTO %v (`%s`) VALUES", ignoreStr, tableName, strings.Join(insertColumn[0], "`,`")))
		if parDef.GetParameterValueAnyToBool(parDef.GetFuncParameterValue(ctx, "lobTableLogo")) {
			var s1 []string
			for i := 0; i < len(insertColumn[0]); i++ {
				if strings.EqualFold(insertColumn[1][i], "gis") {
					s1 = append(s1, "ST_GeomFromText(?, ?)")
				} else {
					s1 = append(s1, "?")
				}
			}
			result.Result = fmt.Sprintf("INSERT INTO %v (`%s`) VALUES (%s)", tableName, strings.Join(insertColumn[0], "`,`"), strings.Join(s1, ","))
		}
		return result, nil
	})
}

func (rows RowsF) writeFileDML(event string, ctx context.Context) (result global.Return, err error) {
	event = fmt.Sprintf("%v -> writeFileDML", event)
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	return ctxDoneExecFunc(ctx, func() (global.Return, error) {
		var schemaTable = getTableName(parDef.GetExecFuncParameterValueBase(ctx))
		result.Sql = parDef.GetParameterValueAnyToString(parDef.GetFuncParameterValue(ctx, parDef.GetDMLSqlParameterValue()))
		var DMLSqlFilePoint = parDef.GetFuncParameterValue(ctx, parDef.GetDMLSqlFilePointParameterValue())
		log.MainLog().Debug(event, schemaTable, fmt.Sprintf(" sql is %v.", result.Sql))
		if DMLSqlFilePoint == nil {
			err = ref.ErrAddPrintf(event, fmt.Errorf("file point is nil"))
			log.ErrorLog().Error(event, schemaTable, fmt.Sprintf(" sql is %v, error is %v.", result.Sql, err))
			return result, err
		}
		if err = DMLSqlFilePoint.(outPut.FileOperations).Write("", result.Sql); err != nil {
			err = ref.ErrAddPrintf(event, err)
			log.ErrorLog().Error(event, schemaTable, fmt.Sprintf(" sql is %v,file name is %v error is %v.", result.Sql, DMLSqlFilePoint.(outPut.FileOperations).Print(outPut.FileName), err))
			return result, err
		}
		log.MainLog().Debug(event, schemaTable, fmt.Sprintf(" sql is %v, file name is %v bath timeOut is %v ms.", result.Sql, DMLSqlFilePoint.(outPut.FileOperations).Print(outPut.FileName), result.TimeOut))
		return result, err
	})
}

func (rows RowsF) connExecDML(event string, ctx context.Context) (result global.Return, err error) {
	event = fmt.Sprintf("%v -> connExecDML", event)
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	return ctxDoneExecFunc(ctx, func() (global.Return, error) {
		result.Sql = parDef.GetParameterValueAnyToString(parDef.GetFuncParameterValue(ctx, parDef.GetDMLSqlParameterValue()))
		result.TimeOut, err = newExecDDLSql(event, getTableName(parDef.GetExecFuncParameterValueBase(ctx)), parDef.GetWriteExecSqlFuncOptionsValueBase(ctx), []string{result.Sql})
		return result, err
	})
}

func (rows RowsF) ExecDML(ctx context.Context) (result global.Return, err error) {
	var event = "[ExecDML]"
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	return ctxDoneExecFunc(ctx, func() (global.Return, error) {
		switch repairMethod := parDef.GetParameterValueAnyToString(parDef.GetFuncParameterValue(ctx, parDef.GetRepairMethodParameterValue())); repairMethod {
		case "table":
			return rows.connExecDML(event, ctx)
		case "file":
			return rows.writeFileDML(event, ctx)
		default:
			err = ref.ErrAddPrintf(event, fmt.Errorf(fmt.Sprintf("expect value not matched. curry value is %v expect value is %v", repairMethod, []string{"table", "file"})))
			log.ErrorLog().Error(event, getTableName(parDef.GetExecFuncParameterValueBase(ctx)), fmt.Sprintf(" error is %v.", err))
		}
		log.MainLog().Debug(event, getTableName(parDef.GetExecFuncParameterValueBase(ctx)), "The ddl sql execution has been completed.")
		return result, err
	})
}

func (rows RowsF) SelectIdxCol(ctx context.Context) (result global.Return, err error) {
	var event = "[SelectIdxCol]"
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	return ctxDoneExecFunc(ctx, func() (global.Return, error) {
		var columnName, asColumnName []string
		for _, i := range parDef.GetParameterValueAnyToStringGarth(parDef.GetFuncParameterValue(ctx, parDef.GetIndexColumnNameParameterValue())) {
			p := getSpecialColNameMetaMap(i, parDef.GetFuncParameterValue(ctx, parDef.GetTableColumnMapParameterValue()).([]parDef.ColMetaMapS))
			asColumnName = append(asColumnName, fmt.Sprintf("C%v", p.ColumnSeq))
			columnName = append(columnName, NewSpecialTypeToFunc(parDef.GetFuncParameterValue(ctx, parDef.GetTableLockColumnFuncParameterValue()).(parDef.Encryption), p))
		}
		result.Result = [][]string{columnName, asColumnName}
		return result, err
	})
}
func (rows RowsF) SelectAllCol(ctx context.Context) (result global.Return, err error) {
	var columnName, asColumnName []string
	var event = "[SelectAllCol]"
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	return ctxDoneExecFunc(ctx, func() (global.Return, error) {
		for _, v := range parDef.GetFuncParameterValue(ctx, parDef.GetTableColumnMapParameterValue()).([]parDef.ColMetaMapS) {
			asColumnName = append(asColumnName, fmt.Sprintf("C%v", v.ColumnSeq))
			columnName = append(columnName, NewSpecialTypeToFunc(parDef.GetFuncParameterValue(ctx, parDef.GetTableLockColumnFuncParameterValue()).(parDef.Encryption), v))
		}
		result.Result = columnName
		return result, err
	})
}
func (rows RowsF) InsertAllCol(ctx context.Context) (result global.Return, err error) {
	var columnNameAndType [][]string
	switch meta := parDef.GetFuncParameterValue(ctx, parDef.GetMetaParameterValue()); meta.(type) {
	case []parDef.ColMetaMapS:
		var columnName, columnType []string
		for _, v := range meta.([]parDef.ColMetaMapS) {
			columnName = append(columnName, v.ColumnName)
			columnType = append(columnType, v.TypeBelong)
		}
		columnNameAndType = append(columnNameAndType, columnName, columnType)
	}
	result.Result = columnNameAndType
	return
}
func (rows RowsF) StringValueCompare(ctx context.Context) (result global.Return, err error) {
	var event = "[StringValueCompare]"
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	var newCompareVal = getColCharacterVarchar(ctx)
	var _, table = parDef.GetExecFuncParameterValueBase(ctx)
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		if result, err = parDef.SqlExec(parDef.GetSqlQueryExecFuncParameterValue(ctx, event, tableSysObjectView, func(o string, s string, t string) string {
			return fmt.Sprintf("SELECT CASE WHEN %v < %v THEN '-1' WHEN %v > %v THEN '1' ELSE '0' END AS comparison_result FROM dual", newCompareVal[0], newCompareVal[1], newCompareVal[0], newCompareVal[1])
		}, QueryColValManage)); err != nil {
			return
		}
		return parDef.ResultExec(parDef.NewGetExternalExecFuncParameterValue(ctx, event, result, result.Result, table))
	}
}

func (rows RowsF) IndexPart(ctx context.Context) (result global.Return, err error) {
	var (
		event          = "[IndexPart]"
		res            global.Return
		selectCol      []string
		selectColAs    []string
		whereCondition []string
		indexP         parDef.IndexPart
	)
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	switch meta := parDef.GetFuncParameterValue(ctx, parDef.GetMetaParameterValue()); meta.(type) {
	case parDef.IndexPart:
		indexP = meta.(parDef.IndexPart)
	default:
		err = ref.ErrAddPrintf("MySQL.IndexPart", errors.New(fmt.Sprintf("type not matched. curry type is %v", reflect.TypeOf(meta))))
		return
	}
	if res, err = rows.SelectIdxCol(ctx); err != nil {
		err = ref.ErrAddPrintf(event, err)
		return
	} else if res.Result == nil {
		return
	} else {
		selectCol, selectColAs = res.Result.([][]string)[0], res.Result.([][]string)[1]
	}
	if whereCondition, err = newWhereGenerate(parDef.GetParameterValueAnyToString(parDef.GetFuncParameterValue(ctx, parDef.GetTableWhereSqlParameterValue())), indexP.WhereGenerate); err != nil {
		err = ref.ErrAddPrintf(event, err)
		return
	}
	if len(whereCondition) == 0 {
		whereCondition = append(whereCondition, fmt.Sprintf("\"%s\" IS NOT NULL ", parDef.GetParameterValueAnyToStringGarth(parDef.GetFuncParameterValue(ctx, parDef.IndexColumnNameParameterValue))[0]))
	}
	var _, table = parDef.GetExecFuncParameterValueBase(ctx)
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		if result, err = parDef.SqlExec(parDef.GetSqlQueryExecFuncParameterValue(ctx, event, tableSysObjectView, func(o string, s string, t string) string {
			switch {
			case parDef.GetParameterValueAnyToInt(parDef.GetFuncParameterValue(ctx, parDef.GetTableLimitParameterValue())) > 0:
				return fmt.Sprintf("select %v from ( "+
					"select %v "+
					"from %v  "+
					"%v %v "+
					") A where 1= 1 "+
					"%v limit %d,1",
					newResultCol(selectColAs, "A"),
					strings.Join(selectCol, ","),
					tableObject(parDef.GetExecFuncParameterValueBaseP(ctx)),
					newOrderByNull(parDef.GetParameterValueAnyToStringGarth(parDef.GetFuncParameterValue(ctx, parDef.IndexColumnNameParameterValue))),
					rowsLimitC(parDef.GetParameterValueAnyToInt(parDef.GetFuncParameterValue(ctx, parDef.GetTableLimitParameterValue()))),
					newWhereSplicing(whereCondition),
					atomic.LoadInt64(indexP.Limit.Pagination))
			default:
				return fmt.Sprintf("select %v "+
					"from %v"+
					"where 1 = 1 %v %v "+
					"limit %d,1",
					strings.Join(selectCol, ","),
					tableObject(parDef.GetExecFuncParameterValueBaseP(ctx)),
					newWhereSplicing(whereCondition),
					newOrderByNull(parDef.GetParameterValueAnyToStringGarth(parDef.GetFuncParameterValue(ctx, parDef.IndexColumnNameParameterValue))),
					atomic.LoadInt64(indexP.Limit.Pagination),
				)
			}
		}, QueryColValManage)); err != nil {
			return
		}
		return parDef.ResultExec(parDef.NewGetExternalExecFuncParameterValue(ctx, event, result, result.Result, table))
	}
}

func (rows RowsF) SelectRowsSql(ctx context.Context) (result global.Return, err error) {
	var (
		res                       global.Return
		w                         = []string{" "}
		event                     = "[SelectRowsSql]"
		selectCol, whereCondition []string
		orderByCol                string
		s                         parDef.IndexPart
		indexP                    parDef.IndexPart
	)
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	switch meta := parDef.GetFuncParameterValue(ctx, parDef.GetMetaParameterValue()); meta.(type) {
	case parDef.IndexPart:
		indexP = meta.(parDef.IndexPart)
	default:
		err = ref.ErrAddPrintf(event, fmt.Errorf("type not matched"))
		//log.ErrorLog().Error(event, getTableName(parameter), fmt.Errorf("type not matched. curry type is %v expect type is %v ", reflect.TypeOf(parameter.Meta), "parDef.IndexPart"))
		return
	}
	return ctxDoneExecFunc(ctx, func() (global.Return, error) {
		if res, err = rows.SelectAllCol(ctx); err != nil {
			err = ref.ErrAddPrintf(event, err)
			return result, err
		} else if res.Result == nil {
			return result, err
		} else {
			selectCol = res.Result.([]string)
		}
		whereCondition, err = newWhereGenerate(parDef.GetParameterValueAnyToString(parDef.GetFuncParameterValue(ctx, parDef.GetTableWhereSqlParameterValue())), indexP.WhereGenerate)
		orderByCol, err = newOrderBY(s.WhereGenerate)
		result.Result = fmt.Sprintf("select %v %v \n"+
			"from %v %v T \n"+
			"where 1 = 1 %v \n"+
			"%v",
			newHitSpecify("", parDef.GetParameterValueAnyToString(parDef.GetFuncParameterValue(ctx, parDef.GetPartitionParameterValue()))), strings.Join(selectCol, ","),
			tableObject(parDef.GetExecFuncParameterValueBaseP(ctx)), specifyScn(parDef.GetParameterValueAnyToString(parDef.GetFuncParameterValue(ctx, parDef.GetTablePositParameterValue()))),
			strings.Join(append(w, whereCondition...), " and "),
			orderByCol)
		log.MainLog().Debug(event, fmt.Sprintf(" sql is %v", result.Result))
		return result, nil
	})
}
func (rows RowsF) ReadData(ctx context.Context) (result global.Return, err error) {
	var execSql []string
	var event = "[ReadData]"
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	if parDef.GetFuncParameterValue(ctx, parDef.GetMetaParameterValue()) == nil {
		log.MainLog().Warn(event, "no data to read")
		return
	}
	switch meta := parDef.GetFuncParameterValue(ctx, parDef.GetMetaParameterValue()); meta.(type) {
	case string:
		execSql = append(execSql, meta.(string))
	case []string:
		execSql = meta.([]string)
	default:
		err = ref.ErrAddPrintf(event, errors.New(fmt.Sprintf("type not matched. curry type %v", reflect.TypeOf(meta))))
		return
	}
	var _, table = parDef.GetExecFuncParameterValueBase(ctx)
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		if result, err = parDef.SqlExec(parDef.GetSqlQueryExecFuncParameterValue(ctx, event, execSql, func(o string, s string, t string) string {
			return o
		}, QueryColValManage)); err != nil {
			return
		}
		log.MainLog().Debug(event, fmt.Sprintf(" sql is %v", execSql))
		return parDef.ResultExec(parDef.NewGetExternalExecFuncParameterValue(ctx, event, result, result.Result, table))
	}
}
func (rows RowsF) ReadMiss(ctx context.Context) (result global.Return, err error) {
	var (
		event = "[ReadMiss]"
		res   global.Return
	)
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	if res, err = rows.SelectAllCol(ctx); err != nil {
		err = ref.ErrAddPrintf(event, err)
		return
	}
	if res.Result == nil {
		log.MainLog().Warn(event, "no data to read")
		return
	}
	var _, table = parDef.GetExecFuncParameterValueBase(ctx)
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		if result, err = parDef.SqlExec(parDef.GetSqlQueryExecFuncParameterValue(ctx, event, tableSysObjectView, func(o string, s string, t string) string {
			return fmt.Sprintf("select %v \n"+
				"from %v  T \n"+
				"where 1= 1 %v",
				strings.Join(res.Result.([]string), ","),
				tableObject(parDef.GetExecFuncParameterValueBaseP(ctx)),
				rowsLimitC(parDef.GetParameterValueAnyToInt(parDef.GetFuncParameterValue(ctx, parDef.GetTableLimitParameterValue()))))
		}, QueryColValManage)); err != nil {
			return
		}
		log.MainLog().Debug(event, fmt.Sprintf(" sql is %v", result.Sql))
		return parDef.ResultExec(parDef.NewGetExternalExecFuncParameterValue(ctx, event, result, result.Result, table))
	}
}

func newColNameLock(schema string, lockFunc string, columnVal string) (r string) {
	r = fmt.Sprintf("%v", columnVal)
	if len(lockFunc) > 0 {
		r = fmt.Sprintf("%v.%v(%v)", schema, lockFunc, r)
	}
	return
}
func newNullValueActive(lob bool, s string, details string) (null bool, r string) {
	switch {
	case strings.EqualFold(s, "<null>"), strings.EqualFold(s, "NULL"):
		null = true
		if lob { //json数据的null值在lob插入是nil
			return null, fmt.Sprintf("<prepare nil>")
		}
		return null, fmt.Sprintf("NULL")
	case strings.EqualFold(s, "<entry>") || len(s) == 0:
		null = true
		switch strings.ToLower(details) {
		case "gis", "decimal", "int":
			return null, fmt.Sprintf("NULL")
		default:
			if lob {
				return null, fmt.Sprintf("<prepare entry>")
			}
			return null, fmt.Sprintf("''")
		}
	default:
		r = s
		return
	}
}
func newGisWktValueCheck(gisVal parDef.GisValWkt, prefixSwitch bool) (prefix string, wkt []string, err error) {
	return GeomJsonToWkt(gisVal, prefixSwitch)
}
func newGisPointStr(s string) (r string, err error) {
	//ST_GeomFromText('POINT(116.3 39.9)', 4326)
	defer func() {
		if err != nil {
			err = ref.ErrAddPrintf("newGisPointStr", err)
		}
	}()
	var gisVal parDef.GisValWkt
	var prefix string
	var newWkt []string
	if gisVal, err = jsonStrToGisValWkt(s); err != nil {
		return
	}
	if prefix, newWkt, err = newGisWktValueCheck(gisVal, false); err != nil {
		return
	}
	r = fmt.Sprintf("ST_GeomFromText('%v%v',%v)", prefix, strings.Join(newWkt, ","), gisVal.SRid)
	return
}
func newGisMultiPointStr(s string) (r string, err error) {
	//ST_GeomFromText('MULTIPOINT(10 10, 20 20, 30 30)', 4326)
	var gisVal parDef.GisValWkt
	var prefix string
	var newWkt []string
	if gisVal, err = jsonStrToGisValWkt(s); err != nil {
		return
	}
	if prefix, newWkt, err = newGisWktValueCheck(gisVal, false); err != nil {
		return
	}
	r = fmt.Sprintf("ST_GeomFromText('%v%v',%v)", prefix, strings.Join(newWkt, ","), gisVal.SRid)
	return
}
func newGisLinestringStr(s string) (r string, err error) {
	//ST_GeomFromText('LINESTRING(116.3 39.9, 116.4 39.9, 116.4 39.8, 116.3 39.8)', 4326)
	var gisVal parDef.GisValWkt
	var prefix string
	var newWkt []string
	if gisVal, err = jsonStrToGisValWkt(s); err != nil {
		return
	}
	if prefix, newWkt, err = newGisWktValueCheck(gisVal, false); err != nil {
		return
	}
	r = fmt.Sprintf("ST_GeomFromText('%v%v',%v)", prefix, strings.Join(newWkt, ","), gisVal.SRid)
	return
}
func newGisMultiLinestringStr(s string) (r string, err error) {
	//ST_GeomFromText('MULTILINESTRING((10 10, 20 20), (30 30, 40 40, 50 30), (60 60, 70 70))', 4326)
	var gisVal parDef.GisValWkt
	var newWkt, wktStr []string
	if gisVal, err = jsonStrToGisValWkt(s); err != nil {
		return
	}
	if _, newWkt, err = newGisWktValueCheck(gisVal, false); err != nil {
		return
	}
	for _, v := range newWkt {
		if strings.HasPrefix(v, "LINESTRING") {
			wktStr = append(wktStr, strings.TrimPrefix(v, "LINESTRING"))
		} else {
			wktStr = append(wktStr, v)
		}
	}
	r = fmt.Sprintf("ST_GeomFromText('MULTILINESTRING(%v)',%v)", strings.Join(wktStr, ","), gisVal.SRid)
	return
}
func newGisPolygonStr(s string) (r string, err error) {
	//ST_GeomFromText('POLYGON((116.3 39.9, 116.4 39.9, 116.4 39.8, 116.3 39.8, 116.3 39.9))', 4326)
	var gisVal parDef.GisValWkt
	var prefix string
	var newWkt []string
	if gisVal, err = jsonStrToGisValWkt(s); err != nil {
		return
	}
	if prefix, newWkt, err = newGisWktValueCheck(gisVal, false); err != nil {
		return
	}
	r = fmt.Sprintf("ST_GeomFromText('%v(%v)',%v)", prefix, strings.Join(newWkt, ","), gisVal.SRid)
	return
}
func jsonStrToGisValWkt(s string) (gisVal parDef.GisValWkt, err error) {
	if err = json.Unmarshal([]byte(s), &gisVal); err != nil {
		return
	}
	return
}
func newGisMultiPolygonStr(s string) (r string, err error) {
	//ST_GeomFromText('MULTIPOLYGON(((10 10, 20 10, 20 20, 10 20, 10 10)), ((30 30, 40 30, 40 40, 30 40, 30 30)))', 4326)
	var gisVal parDef.GisValWkt
	var newWkt []string
	if gisVal, err = jsonStrToGisValWkt(s); err != nil {
		return
	}
	switch gisVal.Type {
	case "polygon":
		if _, newWkt, err = newGisWktValueCheck(gisVal, false); err != nil {
			return
		}
		newWkt = []string{fmt.Sprintf("(%v)", newWkt[0])}
	case "multiPolygon":
		if _, newWkt, err = newGisWktValueCheck(gisVal, false); err != nil {
			return
		}
	}
	r = fmt.Sprintf("ST_GeomFromText('MULTIPOLYGON(%v)',%v)", strings.Join(newWkt, ","), gisVal.SRid)
	return
}
func newGisGeometryStr(s string) (r string, err error) {
	//ST_GeomFromText('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(20 20, 30 30), POLYGON((40 40, 50 40, 50 50, 40 50, 40 40)))', 4326)
	var gisVal parDef.GisValWkt
	var newWkt []string
	var prefix string
	if gisVal, err = jsonStrToGisValWkt(s); err != nil {
		fmt.Println("-------err:", err)
		return
	}
	if prefix, newWkt, err = newGisWktValueCheck(gisVal, false); err != nil {
		return
	}
	r = fmt.Sprintf("ST_GeomFromText('%v(%v)',%v)", prefix, strings.Join(newWkt, ","), gisVal.SRid)
	return
}
func newSpecialGisSubType(s string, v parDef.ColMetaMapS) (r string, err error) {
	if len(s) == 0 || strings.EqualFold(s, "<entry>") || strings.EqualFold(s, "<null>") {
		r = "NULL"
		return
	}
	defer func() {
		if err != nil {
			err = ref.ErrAddPrintf("newSpecialGisSubType", err)
		}
	}()
	switch strings.ToUpper(v.Type.ColTypeMap) {
	case "GEOMETRY", "GEOMETRYCOLLECTION", "GEOMCOLLECTION":
		r, err = newGisGeometryStr(s)
	case "POINT":
		r, err = newGisPointStr(s)
	case "MULTIPOINT":
		r, err = newGisMultiPointStr(s)
	case "LINESTRING":
		r, err = newGisLinestringStr(s)
	case "MULTILINESTRING":
		r, err = newGisMultiLinestringStr(s)
	case "POLYGON":
		r, err = newGisPolygonStr(s)
	case "MULTIPOLYGON":
		r, err = newGisMultiPolygonStr(s)
	}
	return
}
func newSpecialTypeActive(lob bool, s string, v parDef.ColMetaMapS) (r string, err error) {
	//details string
	var event = "[newSpecialTypeActive]"
	null, newS := newNullValueActive(lob, s, v.TypeBelong)
	switch {
	case strings.EqualFold(v.TypeBelong, "time"):
		if !null {
			r = fmt.Sprintf("'%v'", newS)
		} else {
			r = newS
		}
		return
	case strings.EqualFold(v.TypeBelong, "timestamp"):
		// MySQL不允许向timestamp数据类型中插入带时区的日期database error is Error 1292 (22007): Incorrect datetime value: '2024-09-26 11:27:28 +0800 08:00' for column 'V3' at row 1
		if !null {
			r = fmt.Sprintf("'%v'", strings.TrimSpace(strings.Split(newS, " +")[0]))
		} else {
			r = newS
		}
		return
	case strings.EqualFold(v.TypeBelong, "gis"):
		if !null {
			if r, err = newSpecialGisSubType(newS, v); err != nil {
				err = ref.ErrAddPrintf(event, err)
			}
		} else {
			r = newS
		}
		return
	default:
		r = s
		switch {
		case strings.EqualFold(v.TypeBelong, "json") && lob:
			if !null {
				r = fmt.Sprintf("%v", newS)
			} else {
				r = newS
			}
			return
		default:
			if !lob {
				if !null {
					r = fmt.Sprintf("'%v'", s)
				} else {
					r = newS
				}
				return
			}
		}
	}
	return
}
func manyRows(parameter dbPar.GeneralInsertSql) (manyRowsValue []string, lobManyRowsValue [][]*string, err error) {
	var lobLogo = strings.Contains(parameter.Prefix, "(?")
	var rowsData [][]*string
	switch parameter.InsertValue.(type) {
	case [][]*string:
		rowsData = parameter.InsertValue.([][]*string)
	}
	for _, row := range rowsData {
		if len(row) == 0 {
			continue
		}
		var singleRowsValue []string
		var newRow []*string
		for _, v := range parameter.InsertColumn {
			var rowSeq int
		c: //处理源端和目标端得列序号不一致，但列名一致得情况
			for seq, col := range parameter.QueryColumn {
				if strings.EqualFold(col.ColumnName, v.ColumnName) {
					rowSeq = seq
					break c
				}
			}
			var lockSchema, lockFunc string
		l:
			for _, col := range parameter.LockCol.Column {
				if strings.EqualFold(col, v.ColumnName) {
					lockSchema = parameter.Schema
					lockFunc = parameter.LockCol.LockFunc
					break l
				}
			}
			var modifyColVal string
			if modifyColVal, err = newSpecialTypeActive(lobLogo, specialCharacterEscape(row[rowSeq]), v); err != nil {
				return
			}
			columnVal := newColNameLock(lockSchema, lockFunc, modifyColVal)
			singleRowsValue = append(singleRowsValue, columnVal)
			if strings.EqualFold(v.TypeBelong, "gis") && lobLogo { //gis类型 && 包含lob  && gis类型为null，因为ST_GeomFromText(?,?) 需要多追加一个null
				if strings.EqualFold(*row[rowSeq], "<null>") {
					newRow = append(newRow, row[rowSeq], row[rowSeq])
				} else {
					newRow = append(newRow, row[rowSeq])
				}
			} else {
				newRow = append(newRow, row[rowSeq])
			}
		}
		manyRowsValue = append(manyRowsValue, strings.Join(singleRowsValue, ","))
		lobManyRowsValue = append(lobManyRowsValue, newRow)
	}
	ref.SetFinalizer(rowsData)
	return
}
func (rows RowsF) InsertSql(ctx context.Context) (result global.Return, err error) {
	var event = "[InsertSql]"
	var manyRowsValue []string
	var lobManyRowsValue [][]*string
	var parameter dbPar.GeneralInsertSql
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	switch meta := ctx.Value("key"); meta.(type) {
	case dbPar.GeneralInsertSql:
		parameter = meta.(dbPar.GeneralInsertSql)
	default:
		err = ref.ErrAddPrintf(event, errors.New(fmt.Sprintf("type not match. type is %v", reflect.TypeOf(meta))))
		return
	}
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		if manyRowsValue, lobManyRowsValue, err = manyRows(parameter); err != nil {
			err = ref.ErrAddPrintf(event, err)
			return
		}
		if len(manyRowsValue) == 0 {
			return
		}
		if strings.Contains(parameter.Prefix, "(?") {
			result.Result = lobManyRowsValue
			return
		}
		insertSql := fmt.Sprintf("%v (%v)", parameter.Prefix, strings.Join(manyRowsValue, "),("))
		result.Result = &insertSql
		log.MainLog().Debug(event, " sql point is ", result.Result, " sql str is ", insertSql)
		return
	}

}
func (rows RowsF) insertDataPare(parameter dbPar.ExecSql) (execSql any, err error) {
	var setVariables = []string{"SET session unique_checks=0", "SET session foreign_key_checks=0"}
	switch sqlMeta := parameter.Sql; sqlMeta.(type) {
	case []*string:
		var newExecSql []*string
		for _, v := range setVariables {
			newExecSql = append(newExecSql, &v)
		}
		for _, v := range sqlMeta.([]*string) {
			newExecSql = append(newExecSql, v)
		}
		execSql = newExecSql
	case [][]*string:
		execSql = sqlMeta
	default:
		err = ref.ErrAddPrintf("insertDataPare", errors.New(fmt.Sprintf("type not match. type is %v", reflect.TypeOf(sqlMeta))))
		return
	}
	return
}
func (rows RowsF) Insert(ctx context.Context) (result global.Return, err error) {
	var (
		event   = "[Insert]"
		execSql any
	)
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	var parameter dbPar.ExecSql
	switch meta := ctx.Value("key"); meta.(type) {
	case dbPar.ExecSql:
		parameter = meta.(dbPar.ExecSql)
	default:
		err = ref.ErrAddPrintf("Insert", errors.New(fmt.Sprintf("type not match. type is %v", reflect.TypeOf(meta))))
	}
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		if execSql, err = rows.insertDataPare(parameter); err != nil {
			err = ref.ErrAddPrintf(event, err)
			return
		}
		if strings.Contains(parameter.Prefix, "(?") {
			parameter.DbDrive.LobSwitch = true
			parameter.DbDrive.InsertPreFix = parameter.Prefix
		}
		if result, err = newExecInsertSql(event, parameter.DbDrive, execSql); err != nil {
			fmt.Println("---------kdd:", parameter.DbDrive.SqlMode, execSql, err)
		}
		return
	}
}
